drop user project cascade;
create user project identified by project;
grant connect,resource,create session to project;

conn project/project;

Create sequence HomeID start with 1 increment by 1;
Create sequence AgentID start with 1 increment by 1;
Create sequence ApplianceID start with 1 increment by 1;

Create Table Agents
(
	AgentID integer,
	AgentName varchar(30),
	Primary key(AgentID)
);

Create Table Homes
(
	HomeID integer,
	Address varchar(50),
	City varchar(30),
	State varchar(30),
	Zipcode number(5),
	FloorSpace integer,
	Floors integer,
	BedRooms integer,
	Bathrooms integer,
	LandSize integer,
	Year number(4),
	AgentID	integer,
	Primary key(HomeID),
	Foreign key(AgentID) references Agents(AgentID)
);

Create Table Appliances
(
	ApplianceID integer,
	HomeID integer,
	AppType varchar(25),
	Maker varchar(25),
	Primary Key(ApplianceID),
	Foreign key(HomeID) references Homes(HomeID)
);

Create Table HomeOwner
(
	ssn number(9),
	Name varchar(25),
	Dependants integer,
	Income number(8,2),
	Age integer,
	profession varchar(25),
	Primary key(ssn)
);

Create Table HomeOwnership
(
	ssn number(9),
	HomeID integer,
	beginDate date,
	endDate date,
	Foreign key(ssn) references HomeOwner(ssn),
	Foreign key(HomeID) references Homes(HomeID)
);

--INSERTS
insert into homes values (HomeID.NEXTVAL,'30833 Crest Forest','Farmington Hills','Mi',48331,1200,1,2,1,0,2000,null);
insert into homes values (HomeID.NEXTVAL,'10001 Fake Street','Farmington Hills','Mi',48331,1500,2,3,2,1,1990,null);
insert into homes values (HomeID.NEXTVAL,'10002 Fake Street','NOVI','Mi',48331,900,1,1,1,0,1980,null);
insert into homes values (HomeID.NEXTVAL,'10003 Fake Street','Bloomfield','Mi',32451,1000,2,2,1,0,2001,null);
insert into homes values (HomeID.NEXTVAL,'10004 Fake Street','Southfield','Mi',43215,950,2,1,1,2,1972,null);
insert into homes values (HomeID.NEXTVAL,'10005 Fake Street','Detroit','Mi',12234,1500,2,3,2,12,1901,null);
insert into homes values (HomeID.NEXTVAL,'10006 Fake Street','Chicago','IL',54321,6000,3,5,5,15,1987,null);
insert into homes values (HomeID.NEXTVAL,'10007 Fake Street','Dallas','TX',12345,9000,3,6,5,25,2005,null);

insert into agents values (AgentID.NEXTVAL,'Agent1');
insert into agents values (AgentID.NEXTVAL,'Agent2');
insert into agents values (AgentID.NEXTVAL,'Agent3');
insert into agents values (AgentID.NEXTVAL,'Agent4');
insert into agents values (AgentID.NEXTVAL,'Agent5');

insert into appliances values (ApplianceID.NEXTVAL,2,'Refrigerator','Maytag');
insert into appliances values (ApplianceID.NEXTVAL,2,'Stove','Maytag');
insert into appliances values (ApplianceID.NEXTVAL,4,'Washer','Maytag');
insert into appliances values (ApplianceID.NEXTVAL,5,'Dryer','Maytag');
insert into appliances values (ApplianceID.NEXTVAL,6,'Stove','Maytag');

commit;